package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.output.ThresholdingOutputStream;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.JkglDao;

import net.sf.json.JSONObject;

/**
 * @author js
 * @version 创建时间：2016年6月20日
 * @功能：借款人管理界面
 */
@Repository
public class JkglDaoImpl extends P2PJdbcDao implements JkglDao {
	Logger logger = Logger.getLogger(JkglDaoImpl.class);

	/**
	 * @author js JkglDao的实现类 获取借款人借款信息集
	 */

	@Override
	public List queryJkglHkList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if (StringUtils.isNotEmpty(startDate)) {
			startDate = startDate + " 00:00:00";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			endDate = endDate + " 23:59:59";
		}
		String xmmct = params.optString("xmmct");
		String jkrt = params.optString("jkrt");
		String state = params.optString("state");
		String dfrid = params.optString("dfrid");
		String xmType = params.optString("xmtype");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		/*
		 * sql.append(
		 * " SELECT T.*,SUM(YHSY) YHSY FROM (select t1.ID,t1.XMMC,t1.SJSYL,t1.JE,t1.QX,t1.JD,t1.FBSJ,t1.ZT,t1.JKR,t1.DFRID,t1.JKXMJJ,t1.JKYT,t1.DBSM "
		 * ); sql.append(
		 * " ,t1.MJQ,t1.JXRQ,t1.SJSYL_SHOW,t1.XM_TYPE,t1.HK_TYPE,t1.QX_MONTH ");
		 * sql.append(" ,t1.XM_ZC_TYPE,t1.XM_PGJZ,t1.XM_SYED "); sql.append(
		 * " ,t3.DJJE,t3.KYJE,t3.ZHYE,t3.GYH,t3.SJHM"); sql.append(
		 * " ,t1.JKR_CSNF,t1.JKR_ZGXL,t1.JKR_HYZK,t1.JKR_ZNQK,t1.JKR_HJDZ,t1.JKR_ZC_SRSP,t1.JKR_ZC_FCQK,t1.JKR_ZC_XYKZS,t1.JKR_ZC_XYKED "
		 * ); sql.append(
		 * ", t1.JKR_DW_HY,t1.JKR_DW_XZ,t1.JKR_DW_DZ,t1.JKR_DW_ZW, date_format(JKR_DW_RZSJ,'%Y-%m-%d %H:%i:%s') JKR_DW_RZSJ,sum(tcje) MJJE "
		 * ); sql.append(" FROM t_zhfw_zqzrlb t1 "); sql.append(
		 * " LEFT join t_zhfw_tzrxx t2 on t1.ID = t2.ZQID "); sql.append(
		 * " LEFT join t_zhfw_jbxx t3 on t1.DFRID = t3.SJHM ");
		 */
		sql.append("select SUM(YHK_JE) YHSY,t1.ID,t.ZQ_MJJE MJJE,t.DK_DATE");
		sql.append(",t1.XMMC,t1.SJSYL,t1.JE,t1.QX,t1.JD,t1.FBSJ,t1.ZT,t1.JKR,t1.DFRID,t1.JKXMJJ,t1.JKYT");
		sql.append(",t1.DBSM,t1.MJQ,t1.JXRQ,t1.SJSYL_SHOW,t1.XM_TYPE,t1.HK_TYPE,t1.QX_MONTH,t1.XM_ZC_TYPE");
		sql.append(",t1.XM_PGJZ,t1.XM_SYED,t3.DJJE,t3.KYJE,t3.ZHYE,t3.GYH,t3.SJHM,t1.JKR_CSNF,t1.JKR_ZGXL");
		sql.append(
				",t1.JKR_HYZK,t1.JKR_ZNQK,t1.JKR_HJDZ,t1.JKR_ZC_SRSP,t1.JKR_ZC_FCQK,t1.JKR_ZC_XYKZS,t1.JKR_ZC_XYKED");
		sql.append(
				" , t1.JKR_DW_HY,t1.JKR_DW_XZ,t1.JKR_DW_DZ,t1.JKR_DW_ZW, date_format(t1.JKR_DW_RZSJ,'%Y-%m-%d %H:%i:%s') JKR_DW_RZSJ");
		sql.append(" from t_zhfw_tzrxx_mjq_dk t left join t_zhfw_zqzrlb t1 on t1.ID=t.ZQ_ID");
		sql.append(" left join t_zhfw_jbxx t3 on t3.SJHM=t1.DFRID left join t_zhfw_jkr_hk t4");
		sql.append(" on t4.REF_ZQ_ID=t1.ID and t4.HK_STATE='01' and t4.HK_ZL='02'");
		sql.append(" WHERE 1=1 ");
		if (StringUtils.isNotEmpty(dfrid)) {
			sql.append(" AND t1.DFRID = :DFRID ");
			paramMap.put("DFRID", dfrid);
		}
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(t1.FBSJ,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(t1.FBSJ,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(xmmct)) {
			sql.append(" AND t1.XMMC like " + "'%" + xmmct + "%'");
			paramMap.put("XMMC", xmmct);
		}
		if (StringUtils.isNotEmpty(jkrt)) {
			sql.append(" AND t1.JKR like " + "'%" + jkrt + "%'");
			paramMap.put("JKR", jkrt);
		}
		if ((StringUtils.isNotEmpty(state))) {
			sql.append(" AND t1.ZT = :STATE ");
			paramMap.put("STATE", state);
		}
		if (StringUtils.isNotEmpty(xmType)) {
			sql.append(" AND t1.XM_TYPE = :XM_TYPE ");
			paramMap.put("XM_TYPE", xmType);
		}
		/*
		 * sql.append(
		 * " group by t1.ID) T LEFT join (select REF_ZQ_ID,SUM(YHK_JE) YHSY ");
		 * sql.append(
		 * " FROM t_zhfw_jkr_hk WHERE HK_ZL=02 group by ID ) t4 on T.ID = t4.REF_ZQ_ID "
		 * ); sql.append(" group by T.ID order by T.ID desc LIMIT :START,:LIMIT"
		 * );
		 */
		sql.append(" group by t.ID order by t.DK_DATE desc LIMIT :START,:LIMIT");
		logger.info("queryJkglHkList sql is : " + sql.toString());
		logger.info("queryJkglHkList paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	/**
	 * @author js JkglDao的实现类 获取借款人借款信息个数
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getJkglHkCount(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if (StringUtils.isNotEmpty(startDate)) {
			startDate = startDate + " 00:00:00";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			endDate = endDate + " 23:59:59";
		}
		String xmmct = params.optString("xmmct");
		String jkrt = params.optString("jkrt");
		String state = params.optString("state");
		String dfrid = params.optString("dfrid");
		String xmType = params.optString("xmtype");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		/*
		 * sql.append(
		 * " select COUNT(*) FROM (select count(*)  FROM t_zhfw_zqzrlb t1");
		 * sql.append(" left join t_zhfw_tzrxx t2 on t1.ID = t2.ZQID ");
		 * sql.append(" left join t_zhfw_jbxx t3 on t1.DFRID = t3.SJHM ");
		 */
		sql.append("select count(*) from t_zhfw_tzrxx_mjq_dk t left join t_zhfw_zqzrlb t1 ");
		sql.append("on t1.ID=t.ZQ_ID left join t_zhfw_jbxx t3 on t3.SJHM=t1.DFRID");
		sql.append(" WHERE 1=1 ");
		if (StringUtils.isNotEmpty(dfrid)) {
			sql.append(" AND t1.DFRID = :DFRID ");
			paramMap.put("DFRID", dfrid);
		}
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(t1.FBSJ,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(t1.FBSJ,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(xmmct)) {
			sql.append(" AND t1.XMMC like " + "'%" + xmmct + "%'");
			paramMap.put("XMMC", xmmct);
		}
		if (StringUtils.isNotEmpty(jkrt)) {
			sql.append(" AND t1.JKR like " + "'%" + jkrt + "%'");
			paramMap.put("JKR", jkrt);
		}
		if ((StringUtils.isNotEmpty(state))) {
			sql.append(" AND t1.ZT = :STATE ");
			paramMap.put("STATE", state);
		}
		if (StringUtils.isNotEmpty(xmType)) {
			sql.append(" AND t1.XM_TYPE = :XM_TYPE ");
			paramMap.put("XM_TYPE", xmType);
		}
		/* sql.append(" group by t1.ID) T"); */
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	/**
	 * @author js JkglDao的实现类 更新借款人账户余额和可用金额
	 */
	@Override
	public boolean saveJkglHk(JSONObject params) {
		String dfrid = params.optString("dfrid");
		String yhkje = params.optString("yhkje");
		if (StringUtils.isEmpty(dfrid)) {
			logger.info("saveJkglHk gyh 为空");
			return false;
		} else if (StringUtils.isEmpty(yhkje) || "0".equals(yhkje)) {
			logger.info("saveJkglHk yhkje 为0");
			return false;
		} else {
			StringBuffer sql = new StringBuffer();
			Map<String, Object> paramMap = new HashMap<>();
			sql.append("update t_zhfw_jbxx ");
			sql.append(" set KYJE=KYJE-:YHKJE,ZHYE=ZHYE-:YHKJE ");
			sql.append(" where SJHM=:DFRID ");
			paramMap.put("YHKJE", yhkje);
			paramMap.put("DFRID", dfrid);

			logger.info("saveJkglHk sql is " + sql.toString());
			logger.info("saveJkglHk paramMap is " + paramMap);
			this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
			return true;
		}
	}

	/**
	 * @author js JkglDao的实现类 保存还款信息到借款人还款信息表
	 */
	@Override
	public String saveJkrHkxx(JSONObject params) {
		String gyh = params.optString("gyh");
		String sjhm = params.optString("sjhm");
		String refzqid = params.optString("refzqid");
		String refzqxmmc = params.optString("refzqxmmc");
		String refzqxmje = params.optString("refzqxmje");
		String yhkje = params.optString("yhkje");
		String hkstate = params.optString("hkstate");
		String hkzl = params.optString("hkzl");
		String uuid = this.getUUId();
		StringBuffer sql = new StringBuffer();
		sql.append("insert into t_zhfw_jkr_hk(ID,USER_NAME,SJHM,REF_ZQ_ID");
		sql.append(",REF_ZQ_XMMC, REF_ZQ_JE, YHK_JE, HK_DATE, HK_STATE,HK_ZL) ");
		sql.append(" VALUE(:UUID,:USER_NAME,:SJHM,:REF_ZQ_ID,:REF_ZQ_XMMC,:REF_ZQ_JE,:YHK_JE,NOW(),:HK_STATE,:HK_ZL)");

		SqlParameterSource parameters = new MapSqlParameterSource().addValue("UUID", uuid).addValue("USER_NAME", gyh)
				.addValue("SJHM", sjhm).addValue("REF_ZQ_ID", refzqid).addValue("REF_ZQ_XMMC", refzqxmmc)
				.addValue("REF_ZQ_JE", refzqxmje).addValue("YHK_JE", yhkje).addValue("HK_STATE", hkstate)
				.addValue("HK_ZL", hkzl);
		this.logger.info("saveJkrHkxx  sql : " + sql.toString());
		this.logger.info("saveJkrHkxx  parameters : " + parameters.toString());
		this.getNamedParameterJdbcTemplate().update(sql.toString(), parameters);
		return uuid;
	}

	/**
	 * @author js JkglDao的实现类 更新项目状态
	 */
	@Override
	public boolean updateZqzrlbZt(JSONObject params) {
		String id = params.optString("id");
		String xmzt = params.optString("xmzt");
		if (StringUtils.isEmpty(id)) {
			logger.info("updateZqzrlbZt id为空");
			return false;
		}
		StringBuffer sql = new StringBuffer();
		Map<String, String> paramMap = new HashMap<>();
		sql.append("update t_zhfw_zqzrlb ");
		sql.append(" set ZT = :xmzt ");
		sql.append(" where ID = :ID");
		paramMap.put("ID", id);

		paramMap.put("xmzt", xmzt);

		logger.info("updateZqzrlbZt sql is " + sql.toString());
		logger.info("updateZqzrlbZt paramMap is " + paramMap);
		this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
		return true;
	}

	/**
	 * @author js JkglDao的实现类 查询还款详细信息
	 */
	@Override
	public List queryHkmxList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if (StringUtils.isNotEmpty(startDate)) {
			startDate = startDate + " 00:00:00";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			endDate = endDate + " 23:59:59";
		}
		String xmmct = params.optString("xmmct");
		String zqid = params.optString("zqid");
		String state = params.optString("state");
		String hkzl = params.optString("hkzl");
		String sjhm = params.optString("sjhm");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		sql.append(
				" select ID, USER_NAME, SJHM, REF_ZQ_ID, REF_ZQ_XMMC, REF_ZQ_JE, YHK_JE,date_format(HK_DATE,'%Y-%m-%d %H:%i:%s') HK_DATE, HK_STATE,HK_ZL ");
		sql.append(" FROM t_zhfw_jkr_hk ");
		sql.append(" WHERE 1=1 ");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(HK_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(HK_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(xmmct)) {
			sql.append(" AND REF_ZQ_XMMC like " + "'%" + xmmct + "%'");
		}
		if (StringUtils.isNotEmpty(zqid)) {
			sql.append(" AND REF_ZQ_ID = " + "'" + zqid + "'");
		}
		if ((StringUtils.isNotEmpty(state))) {
			sql.append(" AND HK_STATE = :STATE ");
			paramMap.put("STATE", state);
		}
		if ((StringUtils.isNotEmpty(hkzl))) {
			sql.append(" AND HK_ZL = :HK_ZL ");
			paramMap.put("HK_ZL", hkzl);
		}
		if ((StringUtils.isNotEmpty(sjhm))) {
			sql.append(" AND SJHM = :sjhm ");
			paramMap.put("sjhm", sjhm);
		}
		sql.append(" order by HK_DATE desc LIMIT :START,:LIMIT");
		logger.info("queryHkmxList sql is : " + sql.toString());
		logger.info("queryHkmxList paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	/**
	 * @author js JkglDao的实现类 查询还款详细信息条数
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getHkmxCount(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String hkzl = params.optString("hkzl");
		if (StringUtils.isNotEmpty(startDate)) {
			startDate = startDate + " 00:00:00";
		}
		if (StringUtils.isNotEmpty(endDate)) {
			endDate = endDate + " 23:59:59";
		}
		String xmmct = params.optString("xmmct");
		String zqid = params.optString("zqid");
		String state = params.optString("state");
		String sjhm = params.optString("sjhm");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		sql.append(" select count(*) FROM t_zhfw_jkr_hk ");
		sql.append(" WHERE 1=1 ");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(HK_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(HK_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(xmmct)) {
			sql.append(" AND REF_ZQ_XMMC like " + "'%" + xmmct + "%'");
		}
		if (StringUtils.isNotEmpty(zqid)) {
			sql.append(" AND REF_ZQ_ID = " + "'" + zqid + "'");
		}
		if ((StringUtils.isNotEmpty(state))) {
			sql.append(" AND HK_STATE = :STATE ");
			paramMap.put("STATE", state);
		}
		if ((StringUtils.isNotEmpty(hkzl))) {
			sql.append(" AND HK_ZL = :HK_ZL ");
			paramMap.put("HK_ZL", hkzl);
		}
		if ((StringUtils.isNotEmpty(sjhm))) {
			sql.append(" AND SJHM = :sjhm ");
			paramMap.put("sjhm", sjhm);
		}
		logger.info("queryHkmxList sql is : " + sql.toString());
		logger.info("queryHkmxList paramMap is : " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	/**
	 * @author js JkglDao的实现类 添加详细信息到资产详细表
	 */
	@Override
	public boolean addZcDetails(JSONObject params) {
		int id = this.getZcDetailsMaxId();
		id++;
		String username = params.optString("gyh");
		String oJe = params.optString("yhkje");
		String cId = params.optString("hkXxId");
		String oDjje = params.optString("djje");
		String oKyje = params.optString("kyje");
		String oZhye = params.optString("zhye");
		String hkzl = params.optString("hkzl");
		String cJe = "-" + oJe;
		Double fZhye = Double.valueOf(oZhye) - Double.valueOf(oJe);
		Double fKyje = Double.valueOf(oKyje) - Double.valueOf(oJe);
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO t_zhfw_zc_details (ID, USERNAME, ZC_CHANGE_TYPE, ZC_CHANGE_DATE, ZC_CHANGE_JE");
		sql.append(", ZC_FINAL_ZHYE, ZC_CHANGE_ID, ZC_OCCUR_JE, ZC_ORIGINAL_DJJE, ZC_ORIGINAL_KYJE, ZC_ORIGINAL_ZHYE");
		sql.append(", ZC_FINAL_DJJE, ZC_FINAL_KYJE) VALUES (:ID, :USERNAME, :ZC_CHANGE_TYPE, NOW(), :ZC_CHANGE_JE");
		sql.append(
				", :ZC_FINAL_ZHYE, :ZC_CHANGE_ID, :ZC_OCCUR_JE, :ZC_ORIGINAL_DJJE, :ZC_ORIGINAL_KYJE, :ZC_ORIGINAL_ZHYE");
		sql.append(", :ZC_FINAL_DJJE, :ZC_FINAL_KYJE) ");
		Map<String, Object> paramsMap = new HashMap<String, Object>();
		paramsMap.put("ID", id);
		paramsMap.put("USERNAME", username);
		if ("01".equals(hkzl)) {
			paramsMap.put("ZC_CHANGE_TYPE", "26");
		} else if ("02".equals(hkzl)) {
			paramsMap.put("ZC_CHANGE_TYPE", "27");
		}
		paramsMap.put("ZC_CHANGE_JE", cJe);
		paramsMap.put("ZC_FINAL_ZHYE", fZhye);
		paramsMap.put("ZC_CHANGE_ID", cId);
		paramsMap.put("ZC_OCCUR_JE", oJe);
		paramsMap.put("ZC_ORIGINAL_DJJE", oDjje);
		paramsMap.put("ZC_ORIGINAL_KYJE", oKyje);
		paramsMap.put("ZC_ORIGINAL_ZHYE", oZhye);
		paramsMap.put("ZC_FINAL_DJJE", oDjje);
		paramsMap.put("ZC_FINAL_KYJE", fKyje);

		logger.info("addZcDetails sql is : " + sql.toString());
		logger.info("addZcDetails paramMap is : " + paramsMap);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramsMap) > 0;
	}

	@SuppressWarnings("unused")
	private int getZcDetailsMaxId() {
		StringBuffer sql = new StringBuffer();
		Map<String, String> paramMap = new HashMap<>();
		sql.append(" select case when max(convert(id,signed)) is null then 0 ");
		sql.append(" else max(convert(id,signed)) end maxId ");
		sql.append(" from t_zhfw_zc_details ");
		logger.info("t_zhfw_zc_details sql is " + sql.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}

	/**
	 * @author js JkglDao的实现类 查询用户账户信息jbxx,用于数据验证
	 */
	public Map<String, Object> isValidJkglHK(String gyh, String id) {
		StringBuffer sql = new StringBuffer();
		/*
		 * sql.append(" select t2.ZT,SUM(TCJE) MJJE,T1.KYJE FROM t_zhfw_jbxx T1"
		 * ); sql.append(" left join t_zhfw_zqzrlb t2 on t1.sjhm=t2.DFRID ");
		 * sql.append(" LEFT JOIN t_zhfw_tzrxx T3 ON T2.ID=T3.ZQID ");
		 */
		sql.append("select t1.ZT,t3.KYJE,t.ZQ_MJJE MJJE from t_zhfw_tzrxx_mjq_dk t");
		sql.append(" left join t_zhfw_zqzrlb t1 on t1.ID=t.ZQ_ID left join t_zhfw_jbxx t3");
		sql.append(" on t3.SJHM=t1.DFRID");
		sql.append(" WHERE t.USER_NAME=:username AND t.ZQ_ID=:id");
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("username", gyh);
		paramMap.put("id", id);
		logger.info("isValidJkglHK sql is" + sql.toString());
		logger.info("isValidJkglHK paraMap is" + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
	}

	@Override
	public boolean isHkBj(JSONObject params) {
		String sjhm = params.optString("sjhm");
		String refzqid = params.optString("refzqid");
		String sql = "select count(*) from t_zhfw_jkr_hk where sjhm=:sjhm and REF_ZQ_ID=:refzqid and HK_STATE='01' and HK_ZL='01'";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("sjhm", sjhm);
		paramMap.put("refzqid", refzqid);
		logger.info("isHkBj sql is " + sql);
		logger.info("isHkBj paraMap is" + paramMap);
		int count = this.getNamedParameterJdbcTemplate().queryForInt(sql, paramMap);
		if (count > 0) {
			return true;
		}
		return false;
	}

	@Override
	public List<Map<String, Object>> listYhkmx(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String xmmc = params.optString("xmmc");
		String zqid = params.optString("zqid");
		String zt = params.optString("zt");
		String xmType = params.optString("xmType");
		String dfr = params.optString("dfr");
		StringBuffer sql = new StringBuffer();
		sql.append(
				"SELECT t1.ID ZQ_ID,t1.DFRID,t1.XMMC,t1.ZT,t1.XM_TYPE,date_format(t1.FBSJ,'%Y-%m-%d') FBSJ,CONVERT(t1.QX_MONTH,signed) QX_MONTH");
		sql.append(",date_format(date_add(t2.DK_DATE,interval 1 day),'%Y-%m-%d') JXRQ,t1.JE,t2.ZQ_MJJE,t1.SJSYL");
		sql.append(
				",t3.ID,t3.SSQS,date_format(t3.YHLX_DATE,'%Y-%m-%d') YHLX_DATE,t3.YHBJ,t3.YHLX,t3.YHBJ+t3.YHLX HJ FROM d_hd_p2p.t_zhfw_zqzrlb t1 left join");
		sql.append(" t_zhfw_tzrxx_mjq_dk t2 on t1.ID=t2.ZQ_ID and t1.DFRID=t2.SJHM left join t_zhfw_jkr_yhk t3");
		sql.append(" on t1.ID=t3.REF_XM_ID and t3.REF_KU_ID IS NULL where 1=1");
		Map<String, Object> paramMap = new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(t3.YHLX_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(t3.YHLX_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(xmmc)) {
			sql.append(" and t1.XMMC=:xmmc");
			paramMap.put("xmmc", xmmc);
		}
		if (StringUtils.isNotEmpty(zqid)) {
			sql.append(" and t1.ID=:zqid");
			paramMap.put("zqid", zqid);
		}
		if (StringUtils.isNotEmpty(zt)) {
			sql.append(" and t1.ZT=:zt");
			paramMap.put("zt", zt);
		}
		if (StringUtils.isNotEmpty(xmType)) {
			sql.append(" and t1.XM_TYPE=:xmType");
			paramMap.put("xmType", xmType);
		}
		if (StringUtils.isNotEmpty(dfr)) {
			sql.append(" and t1.DFRID=:dfr");
			paramMap.put("dfr", dfr);
		}
		sql.append(" order by convert(t1.ID,signed),convert(t3.SSQS,signed)");
		if (limit > 0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("listYhkmx sql is " + sql.toString());
		logger.info("listYhkmx paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getYhkmxCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String xmmc = params.optString("xmmc");
		String zqid = params.optString("zqid");
		String zt = params.optString("zt");
		String xmType = params.optString("xmType");
		String dfr = params.optString("dfr");
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT count(*) FROM d_hd_p2p.t_zhfw_zqzrlb t1 left join");
		sql.append(" t_zhfw_tzrxx_mjq_dk t2 on t1.ID=t2.ZQ_ID and t1.DFRID=t2.SJHM left join t_zhfw_jkr_yhk t3");
		sql.append(" on t1.ID=t3.REF_XM_ID and t3.REF_KU_ID IS NULL where 1=1");
		Map<String, Object> paramMap = new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(t3.YHLX_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(t3.YHLX_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(xmmc)) {
			sql.append(" and t1.XMMC=:xmmc");
			paramMap.put("xmmc", xmmc);
		}
		if (StringUtils.isNotEmpty(zqid)) {
			sql.append(" and t1.ID=:zqid");
			paramMap.put("zqid", zqid);
		}
		if (StringUtils.isNotEmpty(zt)) {
			sql.append(" and t1.ZT=:zt");
			paramMap.put("zt", zt);
		}
		if (StringUtils.isNotEmpty(xmType)) {
			sql.append(" and t1.XM_TYPE=:xmType");
			paramMap.put("xmType", xmType);
		}
		if (StringUtils.isNotEmpty(dfr)) {
			sql.append(" and t1.DFRID=:dfr");
			paramMap.put("dfr", dfr);
		}
		logger.info("getYhkmxCount sql is " + sql.toString());
		logger.info("getYhkmxCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public int updateYhkmx() {
		String sql = "call d_hd_p2p.p_auto_set_hdb_jkr_yhk";
		Map<String, Object> paramMap = new HashMap<>();
		logger.info("updateYhkmx sql is " + sql);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public List<Map<String, Object>> listMarryHk(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String xmmc = params.optString("xmmc");
		String zqid = params.optString("zqid");
		String dfr = params.optString("dfr");
		StringBuffer sql = new StringBuffer();
		sql.append(
				"select t1.REF_XM_ID,t1.REF_XM_NAME,t1.REF_JKR_USERNAME,ifnull(sum(t1.YHBJ),0)+ifnull(sum(t1.YHLX),0) YHKJE");
		sql.append(
				",convert(t2.QX_MONTH,signed) QX_MONTH,(SELECT ifnull(sum(YHK_JE),0) FROM d_hd_p2p.t_zhfw_jkr_hk where ");
		sql.append("REF_ZQ_ID=t1.REF_XM_ID) HED_JE,t2.DFRID from t_zhfw_jkr_yhk t1 left join t_zhfw_zqzrlb t2 on ");
		sql.append("t1.REF_XM_ID=t2.ID where t1.YHK_XM_TYPE='01' ");
		Map<String, Object> paramMap = new HashMap<>();
		if (StringUtils.isNotEmpty(xmmc)) {
			sql.append(" and t1.REF_XM_NAME like :xmmc");
			paramMap.put("xmmc", "%"+xmmc+"%");
		}
		if (StringUtils.isNotEmpty(zqid)) {
			sql.append(" and t1.REF_XM_ID=:zqid");
			paramMap.put("zqid", zqid);
		}
		if (StringUtils.isNotEmpty(dfr)) {
			sql.append(" and t2.DFRID=:dfr");
			paramMap.put("dfr", dfr);
		}
		sql.append(" group by t1.REF_XM_ID order by t1.REF_XM_ID+0");
		if (limit > 0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("listMarryHk sql is " + sql.toString());
		logger.info("listMarryHk paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getMarryHkCount(JSONObject params) {
		String xmmc = params.optString("xmmc");
		String zqid = params.optString("zqid");
		String dfr = params.optString("dfr");
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from (select t1.REF_XM_ID from t_zhfw_jkr_yhk t1 left join t_zhfw_zqzrlb t2 on ");
		sql.append("t1.REF_XM_ID=t2.ID where t1.YHK_XM_TYPE='01' ");
		Map<String, Object> paramMap = new HashMap<>();
		if (StringUtils.isNotEmpty(xmmc)) {
			sql.append(" and t1.REF_XM_NAME like :xmmc");
			paramMap.put("xmmc", "%"+xmmc+"%");
		}
		if (StringUtils.isNotEmpty(zqid)) {
			sql.append(" and t1.REF_XM_ID=:zqid");
			paramMap.put("zqid", zqid);
		}
		if (StringUtils.isNotEmpty(dfr)) {
			sql.append(" and t2.DFRID=:dfr");
			paramMap.put("dfr", dfr);
		}
		sql.append(" group by t1.REF_XM_ID) t");
		logger.info("getMarryHkCount sql is " + sql.toString());
		logger.info("getMarryHkCount paramMap is " + paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public List<Map<String, Object>> listMarryYhk(JSONObject params) {
		String xmid = params.optString("xmid");
		String username = params.optString("username");
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ID,SSQS,date_format(YHLX_DATE,'%Y-%m-%d') YHLX_DATE,YHBJ,YHLX,YHBJ+YHLX YHK_HJ ");
		sql.append("FROM d_hd_p2p.t_zhfw_jkr_yhk where YHK_QS_STATE in ('1001','1002','1003')");
		Map<String, Object> paramMap = new HashMap<>();
		if (StringUtils.isNotEmpty(xmid)) {
			sql.append(" and REF_XM_ID=:xmid");
			paramMap.put("xmid", xmid);
		}
		if (StringUtils.isNotEmpty(username)) {
			sql.append(" and REF_JKR_USERNAME=:username");
			paramMap.put("username", username);
		}
		sql.append(" order by convert(SSQS,signed)");
		logger.info("listMarryYhk sql is " + sql.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public List<Map<String, Object>> listMarryHedHk(JSONObject params) {
		String xmid = params.optString("xmid");
		String username = params.optString("username");
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ID,date_format(HK_DATE,'%Y-%m-%d %H:%i:%s') HK_DATE,YHK_JE,HK_ZL FROM t_zhfw_jkr_hk where XM_TYPE='01' and HK_QS is null");
		Map<String, Object> paramMap = new HashMap<>();
		if (StringUtils.isNotEmpty(xmid)) {
			sql.append(" and REF_ZQ_ID=:xmid");
			paramMap.put("xmid", xmid);
		}
		if (StringUtils.isNotEmpty(username)) {
			sql.append(" and USER_NAME=:username");
			paramMap.put("username", username);
		}
		sql.append(" order by convert(HK_ZL,signed) desc,date_format(HK_DATE,'%Y-%m-%d')");
		logger.info("listMarryHedHk sql is " + sql.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int updateYhkInfo(Map<String, Object> paramMap) {
		String sql="update t_zhfw_jkr_yhk set YHK_QS_STATE=:state,YHK_YQ_TS=:yqts,YHK_YQ_JE=:yqje,HK_DATE=:hkDate,REF_HK_ID=:hkId where ID=:id";
		logger.info("updateYhkInfo sql is "+sql);
		logger.info("updateYhkInfo paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public int updateHedHkInfo(Map<String, Object> paramMap) {
		String sql="update t_zhfw_jkr_hk set HK_QS=:hkqs,YHBJ=:yhbj,YHLX=:yhlx,YHQT=0 where ID=:id";
		logger.info("updateHedHkInfo sql is "+sql);
		logger.info("updateHedHkInfo paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public Map<String, Object> getYhkInfoById(String id) {
		String sql="select ID,SSQS,date_format(YHLX_DATE,'%Y-%m-%d %H:%i:%s') YHLX_DATE,YHBJ,YHLX,YHBJ+YHLX YHHJ"
				+ ",YHK_QS_STATE,REF_XM_ID, REF_XM_NAME, REF_KU_ID, REF_KU_NAME, REF_JKR_USERNAME,YHK_XM_TYPE "
				+",case when YHK_XM_TYPE='01' then (select ZQ_MJJE from t_zhfw_tzrxx_mjq_dk where ZQ_ID=REF_XM_ID)"
				+ " when YHK_XM_TYPE='02' then (select XM_MJ_FPJE from t_zhfw_h_plan_mjq_dk where"
				+ " REF_H_PLAN_KU_ID=REF_KU_ID) end REF_XM_JE,(select SJHM from t_zhfw_jbxx where gyh=REF_JKR_USERNAME) SJHM"
				+ " from t_zhfw_jkr_yhk where ID='"+id+"'";
		logger.info("getYhkInfoById sql is "+sql);
		return this.getNamedParameterJdbcTemplate().query(sql, new ResultSetExtractor<Map<String, Object>>() {

			@Override
			public Map<String, Object> extractData(ResultSet rs) throws SQLException, DataAccessException {
				Map<String, Object> result=new HashMap<>();
				while (rs.next()) {
					result.put("ID", rs.getString("ID"));
					result.put("hkqs", rs.getString("SSQS"));
					result.put("yhkDate", rs.getString("YHLX_DATE"));
					result.put("yhbj", rs.getString("YHBJ"));
					result.put("yhlx", rs.getString("YHLX"));
					result.put("hkzt", rs.getString("YHK_QS_STATE"));
					result.put("yhhj", rs.getString("YHHJ"));
					result.put("REF_XM_ID", rs.getString("REF_XM_ID"));
					result.put("REF_XM_NAME", rs.getString("REF_XM_NAME"));
					result.put("REF_KU_ID", rs.getString("REF_KU_ID"));
					result.put("REF_KU_NAME", rs.getString("REF_KU_NAME"));
					result.put("REF_JKR_USERNAME", rs.getString("REF_JKR_USERNAME"));
					result.put("YHK_XM_TYPE", rs.getString("YHK_XM_TYPE"));
					result.put("REF_XM_JE", rs.getString("REF_XM_JE"));
					result.put("SJHM", rs.getString("SJHM"));
				}
				return result;
			}
		});
	}

	@Override
	public String getYqInfo(String hkid) {
		StringBuffer sql=new StringBuffer();
		sql.append("select date_format(max(HK_DATE),'%Y-%m-%d %H:%i:%s') HK_DATE from t_zhfw_jkr_hk where 1=1");
		Map<String, Object> paramMap=new HashMap<>();
		if (StringUtils.isNotEmpty(hkid)) {
			String hkids[]=StringUtils.split(hkid, ",");
			sql.append(" and id in(:hkid0");
			paramMap.put("hkid0", hkids[0]);
			for (int i = 1; i < hkids.length; i++) {
				sql.append(" ,:hkid"+i);
				paramMap.put("hkid"+i, hkids[i]);
			}
			sql.append(")");
		}
		logger.info("getYqInfo sql is "+sql.toString());
		logger.info("getYqInfo paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().query(sql.toString(),paramMap, new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				String result=new String();
				while (rs.next()) {
					result=rs.getString("HK_DATE");
				}
				return result;
			}
		});
	}

	@Override
	public int getCompareDays(String endDate, String startDate) {
		String sql="select datediff(:endDate,:startDate) DAYS from dual";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("startDate", startDate);
		paramMap.put("endDate", endDate);
		return this.getNamedParameterJdbcTemplate().query(sql, paramMap, new ResultSetExtractor<Integer>() {

			@Override
			public Integer extractData(ResultSet rs) throws SQLException, DataAccessException {
				while (rs.next()) {
					return rs.getInt("DAYS");
				}
				return 0;
			}
		});
	}

	@Override
	public int isKyHkgn(String xmid) {
		String sql="SELECT count(*) FROM d_hd_p2p.t_zhfw_zqzrlb where id=:xmid and date_format(FBSJ,'%Y-%m-%d')"
				+ "<date_format((select CONFIG_VALUE from d_ccb_fm.t_fm_sys_config where CONFIG_KEY='JKR_HKGN_SXRQ'),'%Y-%m-%d')";
		Map<String, String> paramMap=new HashMap<>();
		paramMap.put("xmid", xmid);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql, paramMap);
	}

	@Override
	public List<Map<String, Object>> listYhkAndDfInfo(JSONObject params) {
		Integer start=params.getInt("start");
		Integer limit=params.getInt("limit");
		String startDate=params.getString("startDate");
		String endDate=params.getString("endDate");
		String startDateReal=params.getString("startDateReal");
		String endDateReal=params.getString("endDateReal");
		String xmType=params.getString("xmType");
		String xmmc=params.getString("xmmc");
		String kumc=params.getString("kumc");
		String hkzt=params.getString("hkzt");
		String username=params.getString("username");
		StringBuffer sql=new StringBuffer();
		sql.append("select ID,SSQS,date_format(YHLX_DATE,'%Y-%m-%d') YHLX_DATE,YHBJ,YHLX,YHBJ+YHLX YHHJ,YHK_XM_TYPE,REF_XM_ID");
		sql.append(",REF_XM_NAME,REF_KU_ID,REF_KU_NAME,REF_JKR_USERNAME,YHK_QS_STATE");
		sql.append(",case when YHK_QS_STATE in ('1001','1002','1003') then if(datediff(now(),YHLX_DATE)<0,0");
		sql.append(",datediff(now(),YHLX_DATE)) else ifnull(YHK_YQ_TS,0) end  YHK_YQ_TS");
		sql.append(" ,case when YHK_QS_STATE in ('1001','1002','1003') then round((YHBJ+YHLX)*if(datediff(now()");
		sql.append(",YHLX_DATE)<0,0,datediff(now(),YHLX_DATE))*0.0005,2) else ifnull(YHK_YQ_JE,0) end YHK_YQ_JE");
		sql.append(",date_format(HK_DATE,'%Y-%m-%d') HK_DATE,REF_HK_ID from t_zhfw_jkr_yhk where 1=1 ");
		Map<String, Object> paramMap=new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(YHLX_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(YHLX_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(startDateReal)) {
			sql.append(" and date_format(HK_DATE,'%Y-%m-%d')>=:startDateReal");
			paramMap.put("startDateReal", startDateReal);
		}
		if (StringUtils.isNotEmpty(endDateReal)) {
			sql.append(" and date_format(HK_DATE,'%Y-%m-%d')<=:endDateReal");
			paramMap.put("endDateReal", endDateReal);
		}
		if (StringUtils.isNotEmpty(xmType)) {
			sql.append(" and YHK_XM_TYPE=:xmType");
			paramMap.put("xmType", xmType);
		}
		if (StringUtils.isNotEmpty(xmmc)) {
			sql.append(" and REF_XM_NAME=:xmmc");
			paramMap.put("xmmc", xmmc);
		}
		if (StringUtils.isNotEmpty(kumc)) {
			sql.append(" and REF_KU_NAME=:kumc");
			paramMap.put("kumc", kumc);
		}
		if (StringUtils.isNotEmpty(hkzt)) {
			sql.append(" and YHK_QS_STATE=:hkzt");
			paramMap.put("hkzt", hkzt);
		}
		if (StringUtils.isNotEmpty(username)) {
			sql.append(" and REF_JKR_USERNAME=:username");
			paramMap.put("username", username);
		}
		sql.append(" order by REF_XM_ID,REF_KU_ID,SSQS");
		if (limit>0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("listYhkAndDfInfo sql is "+sql.toString());
		logger.info("listYhkAndDfInfo paramMap is "+paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getYhkAndDfCount(JSONObject params) {
		String startDate=params.getString("startDate");
		String endDate=params.getString("endDate");
		String startDateReal=params.getString("startDateReal");
		String endDateReal=params.getString("endDateReal");
		String xmType=params.getString("xmType");
		String xmmc=params.getString("xmmc");
		String kumc=params.getString("kumc");
		String hkzt=params.getString("hkzt");
		String username=params.getString("username");
		StringBuffer sql=new StringBuffer();
		sql.append("select count(*) from t_zhfw_jkr_yhk where 1=1 ");
		Map<String, Object> paramMap=new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(YHLX_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(YHLX_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(startDateReal)) {
			sql.append(" and date_format(HK_DATE,'%Y-%m-%d')>=:startDateReal");
			paramMap.put("startDateReal", startDateReal);
		}
		if (StringUtils.isNotEmpty(endDateReal)) {
			sql.append(" and date_format(HK_DATE,'%Y-%m-%d')<=:endDateReal");
			paramMap.put("endDateReal", endDateReal);
		}
		if (StringUtils.isNotEmpty(xmType)) {
			sql.append(" and YHK_XM_TYPE=:xmType");
			paramMap.put("xmType", xmType);
		}
		if (StringUtils.isNotEmpty(xmmc)) {
			sql.append(" and REF_XM_NAME=:xmmc");
			paramMap.put("xmmc", xmmc);
		}
		if (StringUtils.isNotEmpty(kumc)) {
			sql.append(" and REF_KU_NAME=:kumc");
			paramMap.put("kumc", kumc);
		}
		if (StringUtils.isNotEmpty(hkzt)) {
			sql.append(" and YHK_QS_STATE=:hkzt");
			paramMap.put("hkzt", hkzt);
		}
		if (StringUtils.isNotEmpty(username)) {
			sql.append(" and REF_JKR_USERNAME=:username");
			paramMap.put("username", username);
		}
		logger.info("getYhkAndDfCount sql is "+sql.toString());
		logger.info("getYhkAndDfCount paramMap is "+paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public Map<String, Object> getYhkAndDfSum(JSONObject params) {
		String startDate=params.getString("startDate");
		String endDate=params.getString("endDate");
		String startDateReal=params.getString("startDateReal");
		String endDateReal=params.getString("endDateReal");
		String xmType=params.getString("xmType");
		String xmmc=params.getString("xmmc");
		String kumc=params.getString("kumc");
		String hkzt=params.getString("hkzt");
		String username=params.getString("username");
		StringBuffer sql=new StringBuffer();
		sql.append("select ifnull(sum(YHBJ),0) YHBJ,ifnull(sum(YHLX),0) YHLX,ifnull(sum(YHBJ+YHLX),0) YHHJ");
		sql.append(",ifnull(sum(YHK_YQ_JE),0) YHK_YQ_JE from t_zhfw_jkr_yhk where 1=1 ");
		Map<String, Object> paramMap=new HashMap<>();
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(YHLX_DATE,'%Y-%m-%d')>=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(YHLX_DATE,'%Y-%m-%d')<=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(startDateReal)) {
			sql.append(" and date_format(HK_DATE,'%Y-%m-%d')>=:startDateReal");
			paramMap.put("startDateReal", startDateReal);
		}
		if (StringUtils.isNotEmpty(endDateReal)) {
			sql.append(" and date_format(HK_DATE,'%Y-%m-%d')<=:endDateReal");
			paramMap.put("endDateReal", endDateReal);
		}
		if (StringUtils.isNotEmpty(xmType)) {
			sql.append(" and YHK_XM_TYPE=:xmType");
			paramMap.put("xmType", xmType);
		}
		if (StringUtils.isNotEmpty(xmmc)) {
			sql.append(" and REF_XM_NAME=:xmmc");
			paramMap.put("xmmc", xmmc);
		}
		if (StringUtils.isNotEmpty(kumc)) {
			sql.append(" and REF_KU_NAME=:kumc");
			paramMap.put("kumc", kumc);
		}
		if (StringUtils.isNotEmpty(hkzt)) {
			sql.append(" and YHK_QS_STATE=:hkzt");
			paramMap.put("hkzt", hkzt);
		}
		if (StringUtils.isNotEmpty(username)) {
			sql.append(" and REF_JKR_USERNAME=:username");
			paramMap.put("username", username);
		}
		logger.info("getYhkAndDfSum sql is "+sql.toString());
		logger.info("getYhkAndDfSum paramMap is "+paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
	}

	@Override
	public Map<String, Object> getJbxxInfo(String gyh) {
		String sql="select GYH,SJHM,DJJE,KYJE,ZHYE,GWLX,JYMM from t_zhfw_jbxx where GYH='"+gyh+"'";
		logger.info("getJbxxInfo sql is "+sql);
		return this.getNamedParameterJdbcTemplate().query(sql, new ResultSetExtractor<Map<String, Object>>() {

			@Override
			public Map<String, Object> extractData(ResultSet rs) throws SQLException, DataAccessException {
				Map<String, Object> result=new HashMap<>();
				while (rs.next()) {
					result.put("GYH", rs.getString("GYH"));
					result.put("SJHM", rs.getString("SJHM"));
					result.put("DJJE", rs.getString("DJJE"));
					result.put("KYJE", rs.getString("KYJE"));
					result.put("ZHYE", rs.getString("ZHYE"));
					result.put("GWLX", rs.getString("GWLX"));
					result.put("JYMM", rs.getString("JYMM"));
					return result;
				}
				return null;
			}
		});
	}

	@Override
	public String insertYhkDf(Map<String, Object> paramMap) {
		String sql="insert into t_zhfw_jkr_yhk_df(ID,REF_YHK_ID, DFR_USERNAME, JKR_USERNAME, SSQS"
				+ ", YHK_DATE, YHBJ, YHLX, YHK_JE, DF_TYPE) values(:uuid,:REF_YHK_ID, :DFR_USERNAME, :JKR_USERNAME, :SSQS"
				+ ", :YHK_DATE, YHBJ, :YHLX, :YHK_JE, :DF_TYPE)";
		String uuid=this.getUUId();
		paramMap.put("uuid", uuid);
		logger.info("insertYhkDf sql is "+sql);
		logger.info("insertYhkDf paramMap is "+paramMap);
		int count= this.getNamedParameterJdbcTemplate().update(sql, paramMap);
		if (count>0) {
			return uuid;
		} else {
			return null;
		}
	}

	@Override
	public int updateYhkDf(Map<String, Object> paramMap) {
		String sql="update t_zhfw_jkr_yhk_df set REAL_HK_DATE=:REAL_HK_DATE, REAL_YQ_TS=:REAL_YQ_TS"
				+ ", REAL_YQ_JE=:REAL_YQ_JE, REAL_HK_JE=:REAL_HK_JE where ID=:ID";
		logger.info("updateYhkDf sql is "+sql);
		logger.info("updateYhkDf paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public int updateJbxx(double l_zhye, double l_djje, double l_kyje, String username) {
		String sql="update t_zhfw_jbxx set DJJE=:djje,KYJE=:kyje,ZHYE=:zhye where GYH=:username";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("zhye", l_zhye);
		paramMap.put("djje", l_djje);
		paramMap.put("kyje", l_kyje);
		paramMap.put("username", username);
		logger.info("updateJbxx sql is "+sql);
		logger.info("updateJbxx paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public int insertZcDdtails(Map<String, Object> paramMap) {
		String sql="insert into t_zhfw_zc_details(USERNAME, ZC_CHANGE_TYPE, ZC_CHANGE_DATE, ZC_CHANGE_JE"
				+ ", ZC_FINAL_ZHYE, ZC_CHANGE_ID, ZC_OCCUR_JE, ZC_ORIGINAL_DJJE, ZC_ORIGINAL_KYJE"
				+ ", ZC_ORIGINAL_ZHYE, ZC_FINAL_DJJE, ZC_FINAL_KYJE) values(:USERNAME,:ZC_CHANGE_TYPE"
				+ ",now(),:ZC_CHANGE_JE,:ZC_FINAL_ZHYE,:ZC_CHANGE_ID,abs(:ZC_CHANGE_JE),:ZC_ORIGINAL_DJJE"
				+ ",:ZC_ORIGINAL_KYJE,:ZC_ORIGINAL_ZHYE,:ZC_FINAL_DJJE,:ZC_FINAL_KYJE)";
		logger.info("insertZcDdtails sql is "+sql);
		logger.info("insertZcDdtails paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}

	@Override
	public String insertHk(Map<String, Object> paramMap) {
		String sql="insert into t_zhfw_jkr_hk(ID, USER_NAME, SJHM, REF_ZQ_ID, REF_ZQ_XMMC, REF_ZQ_JE"
				+ ", YHK_JE, HK_DATE, HK_STATE, HK_ZL, REF_XM_ID, REF_KU_ID, REF_KU_NAME, REF_KU_JE"
				+ ", XM_TYPE, HK_QS, YHBJ, YHLX, YHQT, IS_DF) values(:uuid,:USER_NAME,:SJHM,:REF_ZQ_ID,:REF_ZQ_XMMC,:REF_ZQ_JE"
				+ ", :YHK_JE,:HK_DATE,:HK_STATE,:HK_ZL,:REF_XM_ID,:REF_KU_ID,:REF_KU_NAME,:REF_KU_JE"
				+ ",:XM_TYPE,:HK_QS,:YHBJ,:YHLX,:YHQT,:IS_DF)";
		String uuid=this.getUUId();
		paramMap.put("uuid", uuid);
		logger.info("insertZcDdtails sql is "+sql);
		logger.info("insertZcDdtails paramMap is "+paramMap);
		int count= this.getNamedParameterJdbcTemplate().update(sql, paramMap);
		if (count>0) {
			return uuid;
		} else {
			return null;
		}
	}

	@Override
	public int updateXmzt(String xmid) {
		String sql="update d_hd_p2p.t_zhfw_zqzrlb set ZT='4' where ID=:xmid";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("xmid", xmid);
		logger.info("updateXmzt sql is "+sql);
		logger.info("updateXmzt paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap);
	}
	
}
